package pe.bf.base.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import pe.bf.base.dao.BaseDAO;
import pe.bf.base.types.OutParameter;
import pe.bf.domain.exceptions.DomainException;

public class BaseDAOImpl implements BaseDAO {
	Logger logger = Logger.getLogger(BaseDAOImpl.class);

	protected DataSource dataSource;

	/**
	 * 
	 * @param dataSource
	 */
	public BaseDAOImpl(DataSource dataSource) {
		super();
		this.dataSource = dataSource;
	}

	/**
	 * Retorna una conexion
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}

	/**
	 * Invoca un store procedure y retorna un resultset
	 * 
	 * @param storeProcedureName
	 * @return
	 * @throws DomainException
	 */
	public boolean invokeStoreProcedure(String storeProcedureName,
			Object... parameters) throws DomainException {

		CallableStatement callableStatement = null;
		boolean result = false;
		Connection connection = null;
		try {
			connection = getConnection();
			callableStatement = connection.prepareCall(storeProcedureName);
			fillStatement(callableStatement, parameters);
			result = callableStatement.execute();
		} catch (SQLException e) {
			logger.error(e, e.fillInStackTrace());
			throw new DomainException(e);
		} finally {
			close(connection);
			close(callableStatement);
		}

		return result;

	}

	public Long invokeStoreProcedureGetId(String storeProcedureName,
			Object... parameters) throws DomainException {

		CallableStatement callableStatement = null;
		Long result = 0L;
		Connection connection = null;
		try {
			connection = getConnection();
			callableStatement = connection.prepareCall(storeProcedureName);
			fillStatementInt(callableStatement, parameters);
			callableStatement.execute();
			for (int i = 0; i < parameters.length; i++) {
				if (parameters[i] instanceof OutParameter) {
					result = Long.valueOf(callableStatement.getObject(i + 1)
							.toString());
				}
			}
		} catch (SQLException e) {
			logger.error(e, e.fillInStackTrace());
			throw new DomainException(e);
		} finally {
			close(connection);
			close(callableStatement);
		}

		return result;

	}

	public Long invokeStoreProcedureGetAbono(String storeProcedureName,
			Object... parameters) throws DomainException {

		CallableStatement callableStatement = null;
		Long result = 0L;
		Connection connection = null;
		try {
			connection = getConnection();
			callableStatement = connection.prepareCall(storeProcedureName);
			fillStatementAbono(callableStatement, parameters);
			callableStatement.execute();
			for (int i = 0; i < parameters.length; i++) {
				if (parameters[i] instanceof OutParameter) {
					result = Long.valueOf(callableStatement.getObject(i + 1)
							.toString());
				}
			}
		} catch (SQLException e) {
			logger.error(e, e.fillInStackTrace());
			throw new DomainException(e);
		} finally {
			close(connection);
			close(callableStatement);
		}

		return result;

	}

	/**
	 * Invoca un store procedure y retorna un resultset
	 * 
	 * @param storeProcedureName
	 * @return
	 * @throws DomainException
	 */
	public ResultSet invokeStoreProcedureRs(String storeProcedureName,
			Object... parameters) throws DomainException {

		CallableStatement callableStatement = null;
		ResultSet resultSet = null;
		Connection connection = null;
		try {
			connection = getConnection();
			callableStatement = connection.prepareCall(storeProcedureName);
			fillStatement(callableStatement, parameters);
			callableStatement.execute();
			for (int i = 0; i < parameters.length; i++) {
				if (parameters[i] instanceof OutParameter) {
					resultSet = (ResultSet) callableStatement.getObject(i + 1);
				}
			}
		} catch (SQLException e) {
			logger.error(e, e.fillInStackTrace());
			e.printStackTrace();
			throw new DomainException(e);
		} finally {
			close(connection);
			close(callableStatement);
		}

		return resultSet;

	}

	public CallableStatement invokeStoreProcedureCallable(
			String storeProcedureName, Object... parameters)
			throws DomainException {

		CallableStatement callableStatement = null;
		Connection connection = null;
		try {
			connection = getConnection();
			callableStatement = connection.prepareCall(storeProcedureName);
			fillStatement(callableStatement, parameters);
			callableStatement.execute();
			return callableStatement;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DomainException(e);
		} finally {

		}

	}

	/**
	 * Fill the <code>CallableStatement</code> replacement parameters with the
	 * given objects.
	 * 
	 * @param stmt
	 *            PreparedStatement to fill
	 * @param params
	 *            Query replacement parameters; <code>null</code> is a valid
	 *            value to pass in.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	public void fillStatement(CallableStatement stmt, Object... params)
			throws SQLException {

		ParameterMetaData pmd = null;

		pmd = stmt.getParameterMetaData();
		int stmtCount = pmd.getParameterCount();
		int paramsCount = params == null ? 0 : params.length;

		if (stmtCount != paramsCount) {
			throw new SQLException("Wrong number of parameters: expected "
					+ stmtCount + ", was given " + paramsCount);
		}

		if (params == null) {
			return;
		}
		out: for (int i = 0; i < params.length; i++) {
			if (params[i] instanceof OutParameter) {
				stmt.registerOutParameter(i + 1,
						((OutParameter) params[i]).getType());
				continue out;
			}
			stmt.setObject(i + 1, params[i] == null ? "" : params[i]);

		}
	}

	public void fillStatementInt(CallableStatement stmt, Object... params)
			throws SQLException {

		ParameterMetaData pmd = null;

		pmd = stmt.getParameterMetaData();
		int stmtCount = pmd.getParameterCount();
		int paramsCount = params == null ? 0 : params.length;

		if (stmtCount != paramsCount) {
			throw new SQLException("Wrong number of parameters: expected "
					+ stmtCount + ", was given " + paramsCount);
		}

		if (params == null) {
			return;
		}
		out: for (int i = 0; i < params.length; i++) {
			if (params[i] instanceof OutParameter) {
				stmt.registerOutParameter(i + 1,
						((OutParameter) params[i]).getType());
				continue out;
			}
			stmt.setObject(i + 1, params[i] == null ? "" : params[i]);
		}
	}

	public void fillStatementAbono(CallableStatement stmt, Object... params)
			throws SQLException {

		ParameterMetaData pmd = null;

		pmd = stmt.getParameterMetaData();
		int stmtCount = pmd.getParameterCount();
		int paramsCount = params == null ? 0 : params.length;

		if (stmtCount != paramsCount) {
			throw new SQLException("Wrong number of parameters: expected "
					+ stmtCount + ", was given " + paramsCount);
		}

		if (params == null) {
			return;
		}

		out: for (int i = 0; i < params.length; i++) {
			if (params[i] instanceof OutParameter) {
				stmt.registerOutParameter(i + 1,
						((OutParameter) params[i]).getType());
				System.out.println("registando parametro out " + i);
				continue out;
			}
			if (i == 8) {
				stmt.setBytes(i + 1, (byte[]) params[i]);
				System.out.println("registando parametro in " + i);
				continue out;
			}
			System.out.println("registando parametro in " + i);
			stmt.setObject(i + 1, params[i]);
		}
	}

	/**
	 * Cierra un <code>Connection</code>, evita el cierre si es nulo.
	 * 
	 * @param connection
	 * @throws SQLException
	 */
	public static void close(Connection connection) throws DomainException {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				throw new DomainException(e);
			}
		}
	}

	/**
	 * cierra un <code>ResultSet</code>, evita el cierre si es nulo
	 * 
	 * @param resultSet
	 * @throws SQLException
	 */
	public static void close(ResultSet resultSet) throws DomainException {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				throw new DomainException(e);
			}
		}
	}

	/**
	 * cierra un <code>Statement</code>, evita el cierre si es nulo.
	 * 
	 * @param stmt
	 * @throws SQLException
	 */
	public static void close(Statement statement) throws DomainException {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				throw new DomainException(e);
			}
		}
	}

}
